package com.weavernorth.sysap.dao;


import com.weavernorth.sysap.bean.*;
import com.weavernorth.sysap.util.CommonToSapUtil;
import com.weavernorth.sysap.util.SyncUtil;
import com.weavernorth.util.Logger4jUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import weaver.conn.ConnStatement;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.BaseBean;
import weaver.general.TimeUtil;
import weaver.general.Util;
import weaver.hrm.job.JobActivitiesComInfo;
import weaver.hrm.resource.ResourceComInfo;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import static com.weavernorth.util.dateUtils.getNowDateTime;

public class SyncOrgStrucDaoImpl implements SyncOrgStrucDao {

    private ConnStatement statement;
    private RecordSetTrans rsTrans;
//    private static Log log = LogFactory.getLog(SyncOrgStrucDaoImpl.class.getName());
    private Logger log = Logger4jUtil.getLoggerByName("/Timmer/synchronization_log/");
    /**
     * 插入分部信息表
     *
     * @param hrmSubCompany 分部信息
     * @return 插入是否成功
     */
    @Override
    public boolean insertHrmSubCompany(HrmSubCompany hrmSubCompany) {
        boolean returnStatus = true;
        statement = new ConnStatement();
        try {
            String strSql_log = "insert into HrmSubCompany (subcompanyname,subcompanydesc,companyid,supsubcomid," +
                    "subcompanycode,canceled) values ('"+hrmSubCompany.getSubCompanyName()+"','"+hrmSubCompany.getSubCompanyDesc()+"'," +
                    ""+hrmSubCompany.getCompanyId()+","+hrmSubCompany.getSupSubComId()+",'"+hrmSubCompany.getSubCompanyCode()+"',"+hrmSubCompany.getCanceled()+")";
            log.info("插入分部的sql:"+strSql_log);
            String strSql = "insert into HrmSubCompany (subcompanyname,subcompanydesc,companyid,supsubcomid," +
                    "subcompanycode,canceled) values (?,?,?,?,?,?)";
            statement.setStatementSql(strSql);
            //分部名称
            statement.setString(1, hrmSubCompany.getSubCompanyName());
            //分部描述
            statement.setString(2, hrmSubCompany.getSubCompanyDesc());
            //所属总部id
            statement.setInt(3, hrmSubCompany.getCompanyId());
            //分部id
            statement.setInt(4, hrmSubCompany.getSupSubComId());
            //分部编码
            statement.setString(5, hrmSubCompany.getSubCompanyCode());
            //是否封存
            statement.setInt(6, hrmSubCompany.getCanceled());
            //顺序,为X时插入99999
//            if ("X".equals(hrmSubCompany.getShowOrder())) {
//                statement.setInt(7, 99999);
//            } else {
//                statement.setInt(7, hrmSubCompany.getShowOrder());
//            }


            statement.executeUpdate();

            returnStatus = true;
        } catch (Exception e) {
            returnStatus = false;
            log.error("添加人力资源分部信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                returnStatus = false;
                log.error("添加人力资源分部信息关闭数据库连接异常！" + e);
            }
        }
        return returnStatus;
    }

    /**
     * 更新分部信息
     *
     * @param hrmSubCompany 分部信息
     * @return
     */
    @Override
    public boolean updateHrmSubCompany(HrmSubCompany hrmSubCompany) {
        boolean returnStatus = true;
        statement = new ConnStatement();
        try {
            //更新分部sql
            StringBuffer strSql = new StringBuffer();

            strSql.append("update HrmSubCompany set subcompanyname=?,subcompanydesc=?,companyid=?,supsubcomid=?,subcompanycode=?, canceled=?");
            //如果顺序为X,则不更新此字段
//            if (!"X".equals(hrmSubCompany.getShowOrder())) {
//                strSql.append(",showorder=?");
//            }
            strSql.append(" where id=?");
            String strSql_up="update HrmSubCompany set subcompanyname='"+hrmSubCompany.getSubCompanyName()+"',subcompanydesc='"+hrmSubCompany.getSubCompanyDesc()+"'," +
                    "companyid="+hrmSubCompany.getCompanyId()+",supsubcomid="+hrmSubCompany.getSupSubComId()+",subcompanycode='"+hrmSubCompany.getSubCompanyCode()+"', canceled="+hrmSubCompany.getCanceled()+"  where id="+hrmSubCompany.getId()+"";
            log.info("更新分部信息的sql:"+strSql_up);
            statement.setStatementSql(strSql.toString());
            //分部名称
            statement.setString(1, hrmSubCompany.getSubCompanyName());
            //分部描述
            statement.setString(2, hrmSubCompany.getSubCompanyDesc());
            //所属总部id
            statement.setInt(3, hrmSubCompany.getCompanyId());
            //
            statement.setInt(4, hrmSubCompany.getSupSubComId());
            statement.setString(5, hrmSubCompany.getSubCompanyCode());
            statement.setInt(6, hrmSubCompany.getCanceled());
            //如果顺序为X,则不更新此字段,赋值顺序也需修改
//            if (!"X".equals(hrmSubCompany.getShowOrder())) {
//                statement.setInt(7, hrmSubCompany.getShowOrder());
//                statement.setString(8, hrmSubCompany.getId());
//            } else {
            statement.setString(7, hrmSubCompany.getId());
//            }

            statement.executeUpdate();
            returnStatus = true;
        } catch (Exception e) {
            returnStatus = false;
            log.error("修改人力资源分部信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                returnStatus = false;
                log.error("修改人力资源分部信息异常！" + e);
            }
        }
        return returnStatus;
    }

    /**
     * 插入部门信息
     *
     * @param hrmDepartment 部门信息
     * @return 插入部门信息是否成功
     */
    @Override
    public boolean insertHrmDepartment(HrmDepartment hrmDepartment) {
        //部门插入返回状态
        boolean depInsRetStatus = true;
        statement = new ConnStatement();
        try {
            String strSql_log = "insert into HrmDepartment (departmentname,departmentmark,subcompanyid1,supdepid," +
                    "departmentcode,canceled,jzglbmfzr,showorder) values ('"+hrmDepartment.getDepartmentName()+"','"+hrmDepartment.getDepartmentMark()+"'," +
                    ""+hrmDepartment.getSubCompanyId()+","+hrmDepartment.getSupDepId()+",'"+hrmDepartment.getDepartmentCode()+"',"+hrmDepartment.getCanceled()+"," +
                    ""+hrmDepartment.getDepartManager()+","+hrmDepartment.getShowOrder()+")";
            log.info("插入部门信息sql:"+strSql_log);
            String strSql = "insert into HrmDepartment (departmentname,departmentmark,subcompanyid1,supdepid," +
                    "departmentcode,canceled,jzglbmfzr,showorder) values (?,?,?,?,?,?,?,?)";
            statement.setStatementSql(strSql);
            //部门名称
            statement.setString(1, hrmDepartment.getDepartmentName());
            //部门标识
            statement.setString(2, hrmDepartment.getDepartmentMark());
            //所属分部id
            statement.setInt(3, hrmDepartment.getSubCompanyId());
            //上级部门id
            statement.setInt(4, hrmDepartment.getSupDepId());
            //部门编码
            statement.setString(5, hrmDepartment.getDepartmentCode());
            //是否封存
            statement.setInt(6, hrmDepartment.getCanceled());
            //部门负责人
            statement.setInt(7, hrmDepartment.getDepartManager());
            //排序,为X时插入99999
            if ("X".equals(hrmDepartment.getShowOrder())) {
                statement.setInt(8, 99999);
            } else {
                statement.setInt(8, hrmDepartment.getShowOrder());
            }

            statement.executeUpdate();
            depInsRetStatus = true;
        } catch (Exception e) {
            depInsRetStatus = false;
            log.error("添加人力资源部门信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                depInsRetStatus = false;
                log.error("添加人力资源部门信息异常！" + e);
            }
        }
        return depInsRetStatus;

    }


    /**
     * 更新部门数据
     *
     * @param hrmDepartment 部门信息
     * @return 更新部门信息是否成功
     */
    @Override
    public boolean updateHrmDepartment(HrmDepartment hrmDepartment) {
        boolean updateDepResStatus = true;
        statement = new ConnStatement();

        try {
            String strSql_log="update HrmDepartment set departmentname='"+hrmDepartment.getDepartmentName()+"',departmentmark='"+hrmDepartment.getDepartmentMark()+"'," +
                    "subcompanyid1="+hrmDepartment.getSubCompanyId()+",supdepid="+hrmDepartment.getSupDepId()+", departmentcode='"+hrmDepartment.getDepartmentCode()+"'," +
                    "canceled="+hrmDepartment.getCanceled()+",jzglbmfzr="+hrmDepartment.getDepartManager()+" where id="+hrmDepartment.getId()+"";
            log.info("更新部门sql:"+strSql_log);
            StringBuffer sql = new StringBuffer();
            sql.append("update HrmDepartment set departmentname=?,departmentmark=?,subcompanyid1=?,supdepid=?, departmentcode=?,canceled=?,jzglbmfzr=?");
            //如果顺序为X,则不更新此字段
            if (!"X".equals(hrmDepartment.getShowOrder())) {
                sql.append(",showorder=?");
            }
            sql.append(" where id=?");
            statement.setStatementSql(sql.toString());
            statement.setString(1, hrmDepartment.getDepartmentName());
            statement.setString(2, hrmDepartment.getDepartmentMark());
            statement.setInt(3, hrmDepartment.getSubCompanyId());
            statement.setInt(4, hrmDepartment.getSupDepId());
            statement.setString(5, hrmDepartment.getDepartmentCode());

            statement.setInt(6, hrmDepartment.getCanceled());
            statement.setInt(7, hrmDepartment.getDepartManager());
            if (!"X".equals(hrmDepartment.getShowOrder())) {
                statement.setInt(8, hrmDepartment.getShowOrder());
                statement.setString(9, hrmDepartment.getId());
            } else {
                statement.setString(8, hrmDepartment.getId());
            }


            // statement.setString(10, hrmDepartment.getDepartmentName());
            statement.executeUpdate();
            updateDepResStatus = true;

        } catch (Exception e) {
            updateDepResStatus = false;
            log.error("修改人力资源部门信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                updateDepResStatus = false;
                log.error("修改人力资源部门信息异常！" + e);
            }
        }
        return updateDepResStatus;
    }


    /**
     * 插入岗位信息到OA表
     */
    @Override
    public boolean insertHrmJobTitle(HrmJobtitles hrmJobtitles) {
        //返回插入岗位表状态
        boolean insertJobResStatus = true;
        statement = new ConnStatement();
        try {
            //插入岗位表sql
            String strSql_log = "insert into HrmJobTitles (jobactivityid,jobtitlemark,jobtitlename,jobtitlecode,jobdepartmentid,departmentcode,jobstatus,modifier) values (" +
                    ""+hrmJobtitles.getJobactivityId()+",'"+hrmJobtitles.getJobtitleMark()+"','"+hrmJobtitles.getJobtitleName()+"','"+hrmJobtitles.getJobtitleCode()+"'," +
                    "'"+hrmJobtitles.getJobdepartmentId()+"','"+hrmJobtitles.getDepartCode()+"','"+hrmJobtitles.getJobtitlesStatus()+"','"+hrmJobtitles.getGwxl()+"')";
            log.info("插入岗位信息sql:"+strSql_log);
            String strSql = "insert into HrmJobTitles (jobactivityid,jobtitlemark,jobtitlename,jobtitlecode,jobdepartmentid,departmentcode,jobstatus,modifier) values (?,?,?,?,?,?,?,?)";
            statement.setStatementSql(strSql);
            //所属职责id
            statement.setInt(1, hrmJobtitles.getJobactivityId());
            //职位标识
            statement.setString(2, hrmJobtitles.getJobtitleMark());
            //岗位名称
            statement.setString(3, hrmJobtitles.getJobtitleName());
            //岗位编码
            statement.setString(4, hrmJobtitles.getJobtitleCode());
            //所属部门id
            statement.setString(5, hrmJobtitles.getJobdepartmentId());
            //部门编码
            statement.setString(6, hrmJobtitles.getDepartCode());
            //岗位状态
            statement.setString(7, hrmJobtitles.getJobtitlesStatus());
            //岗位序列
            statement.setString(8, hrmJobtitles.getGwxl());
            statement.executeUpdate();
            insertJobResStatus = true;
        } catch (Exception e) {
            insertJobResStatus = false;
            log.error("添加人力资源岗位信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                insertJobResStatus = false;
                log.error("添加人力资源岗位信息异常！" + e);
            }
        }
        return insertJobResStatus;
    }


    /**
     * 插入分部至部门表
     *
     * @param strJobtitlesDepartCode
     */
    @Override
    public void insertDepartFromSubcompany(String strJobtitlesDepartCode) {
        RecordSet rs = new RecordSet();
        RecordSet rs1 = new RecordSet();
        //1.查询分部表里是否存在,存在则插入至部门表
        String strSql = "select id,subcompanyname from hrmsubcompany where subcompanycode='" + strJobtitlesDepartCode + "'";
        log.info("查询分部表是否存在sql:" + strSql);
        rs.execute(strSql);
        //如果分部表存在
        if (rs.next()) {
            //分部id
            String strOrgid = Util.null2String(rs.getString("id"));
            //分部名称
            String strOrgname = Util.null2String(rs.getString("subcompanyname"));

            //插入前部门表里是否存在,已存在则不插入
            String departId = getIdByCode("hrmdepartment", "_" + strJobtitlesDepartCode);
            if ("".equals(departId)) {
                //将此分部插入部门表
                //2021-07-02:新增的部门默认canceled=0
                String strInsertSql = "insert into Hrmdepartment(DEPARTMENTNAME,departmentmark,SUBCOMPANYID1,SUPDEPID,DEPARTMENTCODE,canceled) values(?,?,?,?,?,?)";
                rs1.executeUpdate(strInsertSql, "领导班子", "领导班子", strOrgid, "0", "_" + strJobtitlesDepartCode,"0");
                log.info("将分部插入到部门表sql:insert into Hrmdepartment(DEPARTMENTNAME,departmentmark,SUBCOMPANYID1,SUPDEPID,DEPARTMENTCODE,canceled) values(" +
                        "'领导班子','领导班子','"+strOrgid+"','0','_"+strJobtitlesDepartCode+"','0')");
            }
        }


    }


    /**
     * 更新岗位信息表
     *
     * @param hrmJobtitles 岗位信息
     * @return 同步到OA时候成功
     */
    @Override
    public boolean updateHrmJobTitleTable(HrmJobtitles hrmJobtitles) {
        boolean updateJobResStatus = true;
        statement = new ConnStatement();
        try {
            String strSql_log = "update HrmJobTitles set jobtitlemark='"+hrmJobtitles.getJobtitleMark()+"',jobtitlename='"+hrmJobtitles.getJobtitleName()+"'," +
                    "jobtitlecode='"+hrmJobtitles.getJobtitleCode()+"',jobdepartmentid='"+hrmJobtitles.getJobdepartmentId()+"',departmentcode='"+hrmJobtitles.getDepartCode()+"'," +
                    "jobstatus='"+hrmJobtitles.getJobtitlesStatus()+"',modifier='"+hrmJobtitles.getGwxl()+"' where id='"+hrmJobtitles.getId()+"'";
            log.info("更新岗位信息sql:"+strSql_log);
            String sql = "update HrmJobTitles set jobtitlemark=?,jobtitlename=?,jobtitlecode=?,jobdepartmentid=?,departmentcode=?,jobstatus=?,modifier=? where id=?";
            statement.setStatementSql(sql);
            statement.setString(1, hrmJobtitles.getJobtitleMark());
            statement.setString(2, hrmJobtitles.getJobtitleName());
            statement.setString(3, hrmJobtitles.getJobtitleCode());
            statement.setString(4, hrmJobtitles.getJobdepartmentId());
            statement.setString(5, hrmJobtitles.getDepartCode());
            statement.setString(6, hrmJobtitles.getJobtitlesStatus());
            statement.setString(7, hrmJobtitles.getGwxl());
            statement.setString(8, hrmJobtitles.getId());
            statement.executeUpdate();
            updateJobResStatus = true;
        } catch (Exception e) {
            updateJobResStatus = false;
            log.error("修改人力资源岗位信息异常！" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                updateJobResStatus = false;
                log.error("修改人力资源岗位信息异常！" + e);
            }
        }
        return updateJobResStatus;
    }

    /**
     * 插入人员信息
     *
     * @param hrmResource 人员信息
     * @return
     */
    @Override
    public boolean insertHrmResource(HrmResource hrmResource) {
        boolean insResRetSta = true;
        ResourceComInfo rc = null;
        rsTrans = new RecordSetTrans();
          log.info("开始插入人员:"+hrmResource.getLastName());
        try {
            rc = new ResourceComInfo();
            rsTrans.setAutoCommit(false);
            //DATEFIELD1：创建日期;TEXTFIELD1:备注

            //region  [人员同步新增逻辑:2021-06-11]
            //addisplayname    AD显示名称(AD用), 根据员工号取uf_qjt_adxsmc表,xsmc显示名称字段
            //adjobtitles    岗位(AD用) ,取HRMJOBTITLES 岗位中文名称
            //adhrmdepartment    部门(AD用) , 取hrmdepartment的部门中文名称
            //adcompany    公司(AD用),  取到的分部id作为条件去uf_qjt_syb_jc表查询简称
            //endregion
            //插入hrmresource表
            String sqlHrm = "insert into HrmResource  (id,workcode,lastname,sex,jobtitle,enddate,birthday,status,systemlanguage," +
                    "createdate,createrid,departmentid,loginid,managerid,seclevel,isadaccount,mobile,email,subcompanyid1,workstartdate,companystartdate,mobilecall,startdate,DATEFIELD1,TEXTFIELD1," +
                    "addisplayname,adjobtitles,adhrmdepartment,adcompany) " +
                    "values " +
                    "("+hrmResource.getId()+",'"+hrmResource.getWorkCode()+"','"+hrmResource.getLastName()+"','"+hrmResource.getSex()+"','"+hrmResource.getJobtitleId()+"','"+hrmResource.getStrContractEndDate()+"'," +
                    "'"+hrmResource.getBirthday()+"','"+hrmResource.getStatus()+"','"+hrmResource.getSystemLanguage()+"'," +
                    "'"+hrmResource.getCreateDate()+"',1,'"+hrmResource.getDepartmentId()+"'," +
                    "'"+hrmResource.getStrLoginid()+"','"+hrmResource.getManagerId()+"','"+hrmResource.getSeclevel()+"'," +
                    "'"+hrmResource.getStrIsOpenAD()+"','"+hrmResource.getMobile()+"','"+hrmResource.getEmail()+"'," +
                    "'"+hrmResource.getSubcompanyId()+"','"+hrmResource.getStrWorkStartDate()+"','"+hrmResource.getStrCompanyStartDate()+"'," +
                    "'"+hrmResource.getMobilecall()+"','"+hrmResource.getStrContractStartDate()+"','"+getNowDateTime()+"','人员同步',"+
                    "'"+hrmResource.getStrNameAd()+"','"+hrmResource.getStrGwNameAd()+"','"+hrmResource.getStrDepartmentAd()+"','"+hrmResource.getStrCompanytAd()+"')";
            log.info("人员信息插入Sql语句："+sqlHrm);
            rsTrans.executeQuery(sqlHrm);
            String sqlBaseLog = "insert into cus_fielddata(scope,scopeid,id,field10,field11,field12,field13,field14,field23,field32,field62,field57,field69,field25,field26,field27,field29,field30," +
                    "field36,field37,field38,field39,field43,field65,field66,field67,field68,field44,field63,field64,field28,field31,field9,field18,field19,field33,field35,field15,field16," +
                    "field17,field70,field71,field72,field73,field74,field75,field166)" +
                    "values('HrmCustomFieldByInfoType',1,'"+hrmResource.getId()+"','"+hrmResource.getStrNationPlace()+"','"+hrmResource.getStrHukouAddress()+"','"+hrmResource.getStrHukouType()+"'," +
                    "'"+hrmResource.getStrMaritalstatus()+"','"+hrmResource.getStrPolitic()+"','"+hrmResource.getStrSalaryScope()+"'," +
                    "'"+hrmResource.getStrContractType()+"','"+hrmResource.getStrIsOpenEmail()+"','"+hrmResource.getStrCenter()+"'," +
                    "'"+hrmResource.getStrTaxGrouping()+"','"+hrmResource.getStrPayrollCompany()+"','"+hrmResource.getStrSocialSecurityCompany()+"'," +
                    "'"+hrmResource.getStrProvidentFundCompany()+"','"+hrmResource.getStrJoinGroupDate()+"','"+hrmResource.getStrJoinCompanyDate()+"'," +
                    "'"+hrmResource.getStrSalayScope()+"','"+hrmResource.getStrSalaryArea()+"','"+hrmResource.getStrSalaryPositionLevel()+"'," +
                    "'"+hrmResource.getStrSalaryPostGrade()+"','"+hrmResource.getStrPensionBase()+"','"+hrmResource.getStrUnemployeeBase()+"'," +
                    "'"+hrmResource.getStrMedicalBase()+"','"+hrmResource.getStrInjuryBase()+"','"+hrmResource.getStrMaternityBase()+"'," +
                    "'"+hrmResource.getStrProvidentFundBase()+"','"+hrmResource.getStrProvidentFundPlace()+"','"+hrmResource.getStrSocialSecurityPlace()+"'," +
                    "'"+hrmResource.getStrWorkStartDate()+"','"+hrmResource.getStrCompanyStartDate()+"','"+hrmResource.getCertificatenu()+"'," +
                    "'"+hrmResource.getStrBankName()+"','"+hrmResource.getStrBankCardNo()+"','"+hrmResource.getStrCurrectDate()+"'," +
                    "'"+hrmResource.getStrReimbursementLevel()+"','"+hrmResource.getStrDegree()+"','"+hrmResource.getStrEmergency()+"'," +
                    "'"+hrmResource.getStrEmergencyMobile()+"','"+hrmResource.getStrTxgrn()+"','"+hrmResource.getStrLabwk()+"','"+hrmResource.getStrJglx()+"'," +
                    "'"+hrmResource.getStrYglx()+"','"+hrmResource.getStrGzdq()+"','"+hrmResource.getStrGwxl()+"','"+hrmResource.getStrGjfw()+"')";
            log.info("基础信息日志："+sqlBaseLog);
            //插入cus_fielddata 基本信息
            String sqlBaseCus = "insert into cus_fielddata(" +
                    "scope,scopeid,id," +
                    "field10,field11,field12," +
                    "field13,field14,field23," +
                    "field32,field62,field57, " +
                    "field69,field25,field26," +
                    "field27,field29,field30," +
                    "field36,field37,field38," +
                    "field39,field43,field65," +
                    "field66,field67,field68," +
                    "field44,field63,field64," +
                    "field28,field31,field9," +
                    "field18,field19,field33," +
                    "field35,field15,field16," +
                    "field17,field70,field71," +
                    "field72,field73,field74,field75,field166)" +
                    "values(?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?," +
                    "?,?,?,?,?)";
            //执行sql
            rsTrans.executeQuery(sqlBaseCus, "HrmCustomFieldByInfoType", "1", hrmResource.getId(),
                    hrmResource.getStrNationPlace(), hrmResource.getStrHukouAddress(), hrmResource.getStrHukouType(),
                    hrmResource.getStrMaritalstatus(), hrmResource.getStrPolitic(), hrmResource.getStrSalaryScope(),
                    hrmResource.getStrContractType(), hrmResource.getStrIsOpenEmail(), hrmResource.getStrCenter(),
                    hrmResource.getStrTaxGrouping(), hrmResource.getStrPayrollCompany(), hrmResource.getStrSocialSecurityCompany(),
                    hrmResource.getStrProvidentFundCompany(), hrmResource.getStrJoinGroupDate(), hrmResource.getStrJoinCompanyDate(),
                    hrmResource.getStrSalayScope(), hrmResource.getStrSalaryArea(), hrmResource.getStrSalaryPositionLevel(),
                    hrmResource.getStrSalaryPostGrade(), hrmResource.getStrPensionBase(), hrmResource.getStrUnemployeeBase(),
                    hrmResource.getStrMedicalBase(), hrmResource.getStrInjuryBase(), hrmResource.getStrMaternityBase(),
                    hrmResource.getStrProvidentFundBase(), hrmResource.getStrProvidentFundPlace(), hrmResource.getStrSocialSecurityPlace(),
                    hrmResource.getStrWorkStartDate(), hrmResource.getStrCompanyStartDate(), hrmResource.getCertificatenu(),
                    hrmResource.getStrBankName(), hrmResource.getStrBankCardNo(), hrmResource.getStrCurrectDate(),
                    hrmResource.getStrReimbursementLevel(), hrmResource.getStrDegree(), hrmResource.getStrEmergency(),
                    hrmResource.getStrEmergencyMobile(), hrmResource.getStrTxgrn(), hrmResource.getStrLabwk(),hrmResource.getStrJglx(),
                    hrmResource.getStrYglx(),hrmResource.getStrGzdq(),hrmResource.getStrGwxl(),hrmResource.getStrGjfw()
            );

            //插入 cus_fielddata 个人信息表
            String sqlUserLog = "insert into cus_fielddata(scope,scopeid,id,field0,field2," +
                    "field4,field5,field6," +
                    "field7,field72,field73," +
                    "field74,field75,field166) " +
                    "values('HrmCustomFieldByInfoType',-1,'"+hrmResource.getId()+"','"+hrmResource.getStrNation()+"','"+hrmResource.getStrNationality()+"'," +
                    "'"+hrmResource.getStrStaff()+"','"+hrmResource.getStrSubgroup()+"','"+hrmResource.getStrPersonnelScope()+"'," +
                    "'"+hrmResource.getStrPersonnelSubScope()+"','"+hrmResource.getStrJglx()+"','"+hrmResource.getStrYglx()+"'," +
                    "'"+hrmResource.getStrGzdq()+"','"+hrmResource.getStrGwxl()+"','"+hrmResource.getStrGjfw()+"')";
            log.info("个人信息表插入Sql:"+sqlUserLog);
            String sqlUserCus = "insert into cus_fielddata(scope,scopeid,id," +
                    "field0,field2,field4,field5,field6,field7,field72,field73,field74,field75,field166) " +
                    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            //执行sql
            rsTrans.executeQuery(sqlUserCus, "HrmCustomFieldByInfoType", "-1", hrmResource.getId(),
                    hrmResource.getStrNation(), hrmResource.getStrNationality(), hrmResource.getStrStaff(),
                    hrmResource.getStrSubgroup(), hrmResource.getStrPersonnelScope(), hrmResource.getStrPersonnelSubScope(),
                    hrmResource.getStrJglx(),hrmResource.getStrYglx(),hrmResource.getStrGzdq(),
                    hrmResource.getStrGwxl(),hrmResource.getStrGjfw()
            );

            log.info("结束插入人员:"+hrmResource.getLastName());
            //提交事务
            rsTrans.commit();
            insResRetSta = true;
        } catch (Exception e) {
            rsTrans.rollback();
            insResRetSta = false;
            log.error("添加人力资源信息异常！" + e);
        } finally {
            try {

                rc.addResourceInfoCache(hrmResource.getId() + "");
            } catch (Exception e) {
                insResRetSta = false;
                log.error("添加人力资源信息异常！" + e);
            }
        }
        return insResRetSta;
    }

    /**
     * 更新人员信息
     *
     * @param hrmResource 人员信息
     * @return
     */
    @Override
    public boolean updateHrmResource(HrmResource hrmResource) {
        boolean updateResRetSta = true;
        rsTrans = new RecordSetTrans();
        ResourceComInfo rc = null;
        try {
            rc = new ResourceComInfo();
            rsTrans.setAutoCommit(false);

            log.info("开始更新人员:" + hrmResource.getId());
            log.info("部门id:" + hrmResource.getDepartmentId() + ",岗位id:" + hrmResource.getJobtitleId());
            log.info("AD显示名:" + hrmResource.getStrNameAd() + ",AD岗位:" + hrmResource.getStrGwNameAd());
            log.info("AD部门："+hrmResource.getStrDepartmentAd()+",AD公司名："+hrmResource.getStrCompanytAd());

//hrmResource.getJobtitleId()
            //DATEFIELD2:更新日期
            //更新hrmresource表
            String sqlHrm = "update HrmResource set workcode=?,lastname=?,sex=?,jobtitle=?," +
                    "enddate=?,birthday=?,status=?,departmentid=?,loginid=?,managerid=?,seclevel=?,isadaccount=?,mobile=?,email=?,subcompanyid1=?  " +
                    ",workstartdate=?,companystartdate=?,mobilecall=?,startdate=?,DATEFIELD2=?,addisplayname=?,adjobtitles=?,adhrmdepartment=?,adcompany=? where id = ?";
            rsTrans.executeQuery(sqlHrm, hrmResource.getWorkCode(), hrmResource.getLastName(), hrmResource.getSex(),
                    hrmResource.getJobtitleId(), hrmResource.getStrContractEndDate(), hrmResource.getBirthday(),
                    hrmResource.getStatus(), hrmResource.getDepartmentId(), hrmResource.getStrLoginid(),
                    hrmResource.getManagerId(), hrmResource.getSeclevel(), hrmResource.getStrIsOpenAD(),
                    hrmResource.getMobile(), hrmResource.getEmail(), hrmResource.getSubcompanyId(),
                    hrmResource.getStrWorkStartDate(), hrmResource.getStrCompanyStartDate(),hrmResource.getMobilecall(),
                    hrmResource.getStrContractStartDate(),getNowDateTime(),hrmResource.getStrNameAd(),hrmResource.getStrGwNameAd(),hrmResource.getStrDepartmentAd(),hrmResource.getStrCompanytAd(),
                    hrmResource.getId());
            //更新cus_fielddata
            //说明：因为无法获取scopeid,无法确定更新哪条数据,所以选择全部更新
            String sqlCus = "update  cus_fielddata set" +
                    " field0=?,field2=?,field10=?," +
                    "field11=?,field12=?,field13=?," +
                    "field14=?,field4=?,field5=?," +
                    "field6=?,field7=?,field23=?," +
                    "field32=?,field62=?,field57=?, " +
                    "field69=?,field25=?,field26=?," +
                    "field27=?,field29=?,field30=?," +
                    "field36=?,field37=?,field38=?," +
                    "field39=?,field43=?,field65=?," +
                    "field66=?,field67=?,field68=?," +
                    "field44=?,field63=?,field64=?, " +
                    "field28=?,field31=?,field24=?, " +
                    "field9=?,field18=?,field19=?, " +
                    "field33=?,field35=?,field15=?," +
                    " field16=?,field17=? ,field70=?," +
                    " field71=?,field72=?,field73=?,field74=?,field75=?,field166=?" +
                    "where id=?";
            //执行sql
            log.info("当前工资核算范围:" + hrmResource.getStrSalaryScope());
            rsTrans.executeQuery(sqlCus,
                    hrmResource.getStrNation(), hrmResource.getStrNationality(), hrmResource.getStrNationPlace(),
                    hrmResource.getStrHukouAddress(), hrmResource.getStrHukouType(), hrmResource.getStrMaritalstatus(),
                    hrmResource.getStrPolitic(), hrmResource.getStrStaff(), hrmResource.getStrSubgroup(),
                    hrmResource.getStrPersonnelScope(), hrmResource.getStrPersonnelSubScope(), hrmResource.getStrSalaryScope(),
                    hrmResource.getStrContractType(), hrmResource.getStrIsOpenEmail(), hrmResource.getStrCenter(),

                    hrmResource.getStrTaxGrouping(), hrmResource.getStrPayrollCompany(), hrmResource.getStrSocialSecurityCompany(),
                    hrmResource.getStrProvidentFundCompany(), hrmResource.getStrJoinGroupDate(), hrmResource.getStrJoinCompanyDate(),
                    hrmResource.getStrSalayScope(), hrmResource.getStrSalaryArea(), hrmResource.getStrSalaryPositionLevel(),
                    hrmResource.getStrSalaryPostGrade(), hrmResource.getStrPensionBase(), hrmResource.getStrUnemployeeBase(),
                    hrmResource.getStrMedicalBase(), hrmResource.getStrInjuryBase(), hrmResource.getStrMaternityBase(),
                    hrmResource.getStrProvidentFundBase(), hrmResource.getStrProvidentFundPlace(), hrmResource.getStrSocialSecurityPlace(),
                    hrmResource.getStrWorkStartDate(), hrmResource.getStrCompanyStartDate(), hrmResource.getStrSocial(),
                    hrmResource.getCertificatenu(), hrmResource.getStrBankName(), hrmResource.getStrBankCardNo(),
                    hrmResource.getStrCurrectDate(), hrmResource.getStrReimbursementLevel(), hrmResource.getStrDegree(),
                    hrmResource.getStrEmergency(), hrmResource.getStrEmergencyMobile(), hrmResource.getStrTxgrn(),
                    hrmResource.getStrLabwk(),hrmResource.getStrJglx(),hrmResource.getStrYglx(),hrmResource.getStrGzdq(),hrmResource.getStrGwxl(),hrmResource.getStrGjfw(),
                    hrmResource.getId());

             log.info("结束更新人员:"+hrmResource.getLastName());
            rsTrans.commit();
            updateResRetSta = true;
        } catch (Exception e) {
            updateResRetSta = false;
            rsTrans.rollback();
            log.error("更新人力资源信息异常！" + e);
        } finally {
            try {
                rc.updateResourceInfoCache(hrmResource.getId() + "");
            } catch (Exception e) {
                updateResRetSta = false;
                rsTrans.rollback();
                log.error("更新人力资源信息异常！" + e);
            }
        }
        return updateResRetSta;
    }


    /***
     * 通过编码转ID
     * @param strTablename 表名
     * @param strCode    编码
     * @return
     */
    @Override
    public String getIdByCode(String strTablename, String strCode) {
        RecordSet rs = null;
        String strReturnValue = "";
        if (null == strTablename || "".equals(strTablename)) {
            return strReturnValue;
        }
        String strTablenameTemp = "";
        String strColumnValue = "id";
        String strCodeColumnName = "";
        // 分部
        if (strTablename.equalsIgnoreCase("hrmsubcompany")) {
            strCodeColumnName = "subcompanycode";
        }
        // 部门
        else if (strTablename.equalsIgnoreCase("hrmdepartment")) {
            strCodeColumnName = "departmentcode";
        }
        //部门建模表
        else if (strTablename.equalsIgnoreCase("uf_sapgw")) {
            strCodeColumnName = "gwbm";
        }
        //部门封存表
        else if (strTablename.equalsIgnoreCase("hrmdepartmentcanceled")) {
            strTablenameTemp = strTablename;
            strTablename = strTablename.replace("canceled", "");
            strColumnValue = strColumnValue + "," + "canceled";
            strCodeColumnName = "departmentcode";
        }
        // 部门自定义表
        else if (strTablename.equalsIgnoreCase("hrmdepartmentdefined")) {
            strCodeColumnName = "deptid";
        }
        // 岗位
        else if (strTablename.equalsIgnoreCase("hrmjobtitles")) {
            strCodeColumnName = "jobtitlecode";
        }
        // 人员
        else if (strTablename.equalsIgnoreCase("hrmresource")) {
            strCodeColumnName = "workcode";
        }
        //自定义数据表
        else if (strTablename.equalsIgnoreCase("cus_fielddata")) {
            strCodeColumnName = Util.null2String(new BaseBean().getPropValue("SsoLoginInfo", "hronlysignfield"));
        }
        //人力资源总部表
        else if (strTablename.equalsIgnoreCase("HrmCompany")) {
            strCodeColumnName = "companydesc";
        } else {
            strCodeColumnName = "error";
        }
        try {
            rs = new RecordSet();
            //根据编码查询各个表id
            String strSql = "select " + strColumnValue + " from " + strTablename + " where " + strCodeColumnName + " = '" + strCode + "'";
            //如果为人员表,则查询为主账号的人员id
            if ("hrmresource".equalsIgnoreCase(strTablename)) {
                strSql = strSql + " and (accounttype is null or accounttype !='1')";
            }else if (strTablename.equalsIgnoreCase("hrmdepartment")) {
                strSql = strSql + " and canceled=0 ";//部门启用
            }else if(strTablename.equalsIgnoreCase("hrmsubcompany")){
                strSql = strSql + " and canceled=0 ";//分部启用
            }
            rs.execute(strSql);
            log.info("通过编码转ID的sql:"+strSql);
            if (rs.next()) {
                //返回id的值
                strReturnValue = Util.null2String(rs.getString("id"));
                if (strTablenameTemp.equalsIgnoreCase("hrmdepartmentcanceled")) {
                    String canceled = Util.null2String(rs.getString("canceled"));
                    if (canceled.equals("1")) {
                        strReturnValue = "canceled";
                    }
                }
            }
        } catch (Exception e) {
            log.info("error in getIdByCode");
            log.info("tablename=" + strTablename + ",code=" + strCode);
        }
        return strReturnValue;
    }

    /**
     * 岗位职务是否存在
     *
     * @param name 职务名
     * @return 职务id
     */
    @Override
    public String jobActivitieIsRepeat(String name) {
        String returnValue = "";

        RecordSet rs = null;
        try {
            rs = new RecordSet();
            rs.executeQuery("select id from HrmJobActivities where jobactivityname = '" + name + "'");
            if (rs.next()) {
                returnValue = Util.null2String(rs.getString("id"));
            }

            if ("".equals(returnValue)) {
                returnValue = this.insertHrmJobActivities(name);
            }
        } catch (Exception e) {

        }
        return returnValue;

    }

    /**
     * 根据职务类型的中文名获取职务类型的id
     *
     * @return 职务类型的id
     */
    @Override
    public int getJobActivitiesType() {
        int jobgroupId = 0;
        String jobactivitiesType = CommonToSapUtil.getPropInfoWithChinese("SsoLoginInfo",
                "jobactivitiestype", "utf-8");

        RecordSet rs = new RecordSet();
        rs.executeQuery("select id from hrmjobgroups where jobgroupname = '" + jobactivitiesType + "'");
        if (rs.next()) {
            jobgroupId = Util.getIntValue((rs.getString("id")), 0);
        }

        return jobgroupId;
    }

    /**
     * 如果不存在职务，则插入该名字的职务
     *
     * @param name 职务名
     * @return 职务的id
     */
    @Override
    public String insertHrmJobActivities(String name) {
        String receiveValue = "";


        statement = new ConnStatement();
        JobActivitiesComInfo jc = null;
        try {
//            RecordSet rs = new RecordSet();
//            rs.executeSql("");
            jc = new JobActivitiesComInfo();
            String sql = "insert into HrmJobActivities (jobgroupid,jobactivityname,jobactivitymark) values (?,?,?)";
            statement.setStatementSql(sql);
            statement.setInt(1, this.getJobActivitiesType());
            statement.setString(2, name);
            statement.setString(3, name);
            statement.executeUpdate();

        } catch (Exception e) {
            log.error("添加人力资源职责信息异常！" + e);
        } finally {
            try {
                statement.close();
                receiveValue = this.getActivitieMaxId() + "";
                jc.removeJobActivitiesCache();
            } catch (Exception e) {
                log.error("添加人力资源职责信息异常！" + e);
            }
        }
        return receiveValue;
    }

    /**
     * 获取新增职务后，最大的id值
     *
     * @return
     */
    @Override
    public int getActivitieMaxId() {
        int returnValue = 0;
        RecordSet rs = new RecordSet();
        try {
            rs.executeQuery("select max(id) as id from HrmJobActivities");
            if (rs.next()) {
                returnValue = Util.getIntValue((rs.getString("id")), 0);
            }
        } catch (Exception e) {
            log.error("得到最大职务ID失败！" + e);
        }
        return returnValue;

    }

    /**
     * 根据部门ID得到所属分部ID
     *
     * @param depId 部门id
     * @return
     */
    @Override
    public String getSubIdByDepId(String depId) {
        String returnValue = "";
        try {
            RecordSet rs = new RecordSet();
            rs.executeQuery("select subcompanyid1 from HrmDepartment where id =" + depId);
            if (rs.next()) {
                returnValue = Util.null2String(rs.getString("subcompanyid1"));
            }
        } catch (Exception e) {
            log.error("根据部门ID得到所属分部ID错误！" + e);
        }
        return returnValue;
    }

    /**
     * 获取直接上级Id和所有上级
     *
     * @param managerid 上级标记值，eg:编号
     * @param managerid 关键字段
     * @return
     */
    @Override
    public String getManagerIdAndStr(Logger log,String managerid) {
        RecordSet rs = new RecordSet();
        String returnStr = "";
        if (null == managerid || "".equals(managerid)) {
            return returnStr;
        }
        try {
            rs.executeQuery("select managerstr from HrmResource where id = " + managerid);
            String managerstr = "";
            String managerId = managerid;
            if (rs.next()) {
                managerstr = Util.null2String(rs.getString("managerstr"));
                if (managerstr.equals("")) {
                    managerstr = "" + managerId;
                } else {
                    managerstr = managerstr + "," + managerId;
                }
            }
            SyncUtil.removeDuplicate(log,managerstr);
        } catch (Exception e) {
            log.error("查找人员所有上级异常！" + e);
        }
        return returnStr;
    }

    /***
     * 得到人员最大ID
     *
     * @return
     */
    @Override
    public int getHrmMaxid() {
        int maxID = 1;
        RecordSet rs = new RecordSet();
        try {
            rs.executeProc("HrmResourceMaxId_Get", "");
            if (rs.next()) {
                maxID = rs.getInt(1);
            }
        } catch (Exception e) {
            log.error("得到最大的人员ID错误(插入)！" + e);
        }
        return maxID;
    }

    /**
     * 插入同步结果记录到同步日志表
     *
     * @param syncLog 同步日志数据
     */
    @Override
    public void insertSyncLog(SyncLog syncLog) {
        statement = new ConnStatement();
        String strUuid = UUID.randomUUID().toString().replaceAll("-", "");
        try {
            //模块id
            int modeId = getModeIdByTableName("uf_sjtbrz");

            String sql = "insert into uf_sjtbrz (syncode,syncname,synctype,syncinfotype,syncinfodesc,syncdate,synctime" +
                    ",modedatacreater,formmodeid,modedatacreatertype,uuid) " +
                    "values (?,?,?,?,?,?,?,?,?,?,?)";
            statement.setStatementSql(sql);
            statement.setString(1, syncLog.getSyncCode());
            statement.setString(2, syncLog.getSyncName());
            statement.setInt(3, syncLog.getSyncType());
            statement.setInt(4, syncLog.getSyncInfoType());
            statement.setString(5, syncLog.getSyncInfoDesc());
            statement.setString(6, syncLog.getSyncDate());
            statement.setString(7, syncLog.getSyncTime());
            statement.setInt(8, 1);
            statement.setInt(9, modeId);
            statement.setInt(10, 0);
            statement.setString(11, strUuid);
            statement.executeUpdate();
            // 授权在serviceimpl里线程批量授权
        } catch (Exception e) {
            log.error("写入同步日志时异常！数据：" + syncLog.getSyncCode() + "、" + syncLog.getSyncName() + "、" +
                    syncLog.getSyncType() + ";错误原因：" + e);
        } finally {
            try {
                statement.close();
            } catch (Exception e) {
                log.error("写入日志时关闭异常！数据：" + syncLog.getSyncCode() + "、" + syncLog.getSyncName() +
                        "、" + syncLog.getSyncType() + ";错误原因：" + e);
            }
        }
    }

    /**
     * 通过建模表单获取建模模块id
     *
     * @param tableName 表名
     * @return 该表对应的模块id
     */
    public static int getModeIdByTableName(String tableName) {
        RecordSet rs = new RecordSet();
        String result = "1";
        //查询表单建模模块id
        String selSql = " select id from modeinfo where formid = (select id from workflow_bill where tablename = '" +
                tableName + "')";

        rs.executeQuery(selSql);
        if (rs.next()) {
            result = rs.getString("id");
        }

        return Util.getIntValue(result);
    }


    /**
     * 查询部门表里是否存在,处理部门变为分部的数据
     *
     * @param strCompanyCode 编码
     */
    @Override
    public void dealDepartment(String strCompanyCode) {
        log.info("部门变分部code:" + strCompanyCode);
        //数据库操作
        RecordSet rs = new RecordSet();
        //查询部门表里是否存在
        String strDepartId = Util.null2String(getIdByCode("Hrmdepartment", strCompanyCode));
        //log.info("部门id:"+strDepartId);
        //如果存在则把部门封存,部门变为分部
        if (!"".equals(strDepartId)) {
            // log.info("部门变分部开始:"+strCompanyCode);
            String updateSql = "update Hrmdepartment set canceled=1 where id=?";
            log.info("封存部门sql==" + updateSql);
            rs.executeUpdate(updateSql, strDepartId);
            //更新部门上级为此部门的为无上级部门
            String updateSubdepSql = "update Hrmdepartment set supdepid=0 where supdepid=?";
            rs.executeQuery(updateSubdepSql, strDepartId);
        }
    }

    /**
     * 查询分部表是否存在此部门编码,有则处理为封存
     */

    @Override
    public void dealSubcompany(String strDepartCode) {
        //log.info("分部变部门code:"+strDepartCode);
        //数据库操作
        RecordSet rs = new RecordSet();

        //查询部门表里的id和上级id
        String sqlId = "select id ,supsubcomid from hrmsubcompany where  subcompanycode=?";
        rs.executeQuery(sqlId, strDepartCode);
        rs.next();
        //分部id
        String strSubcomId = Util.null2String(rs.getString("id"));
        /// log.info("当前分部id:"+strSubcomId);
        //上级分部id
        String strSupsubid = Util.null2String(rs.getString("supsubcomid"));
        //log.info("当前上级分部id:"+strSupsubid);
        //如果存在则把分部封存,部门变为分部
        if (!"".equals(strSubcomId)) {
            //  log.info("分部变部门开始:"+strDepartCode);
            String updateSql = "update Hrmsubcompany set canceled=1 where id=?";
            log.info("封存分部sql==" + updateSql);
            rs.executeUpdate(updateSql, strSubcomId);
            //更新其他分部上级分部为此分部的上级分部
            String updateSubSql = "update Hrmsubcompany set supsubcomid=? where supsubcomid=?";
            rs.executeUpdate(updateSubSql, strSupsubid, strSubcomId);
            //更新部门的分部为此分部的分部为上级分部
            String updateDepartSql = "update Hrmdepartment set subcompanyid1=? where subcompanyid1=?";
            rs.executeUpdate(updateDepartSql, strSupsubid, strSubcomId);
        }

    }


}
